//
//  SQLiteManager.swift
//  SCUTWiki
//
//  Created by Wu Weisong on 2020/12/10.
//  Copyright © 2020 wwszhw. All rights reserved.
//

import Foundation
import SQLite3

class SQLiteManager: NSObject {
    private var dbPath:String!    //数据库所存储的文件所在路径
    private var database:OpaquePointer?=nil   //数据库，是一个指针，指向一个结构体
    private let DB_NAME:String = "wiki"
    
    //共享一个实例化对象 —————— 单例变量
    static var shareInstance:SQLiteManager{
        return SQLiteManager()
    }
    
    override init() {
        let fileManager = FileManager.default
        let documentsUrl = fileManager.urls(for: .documentDirectory, in: .userDomainMask)
        let finalDatabaseURL = documentsUrl.first!.appendingPathComponent("wiki.sqlite")
        //dbPath = Bundle.main.path(forResource: DB_NAME, ofType: "sqlite")
        dbPath = finalDatabaseURL.absoluteString
    }
    
    //打开数据库
    func openDB() -> Bool {
        let result = sqlite3_open(dbPath, &database)

        if result != SQLITE_OK{
            print("打开数据库失败")
            return false
        }
        return true
    }
    
    //关闭数据库
    func closeDB(){
        sqlite3_close(database)
    }
    
    /**
        执行数据库语句
            1. 查询
    **/
    func execQuerySQL(sql:String)->[[String:AnyObject]]?{
        let cSql = sql.cString(using: String.Encoding.utf8)!  //对sql语句进行utf8编码
        var statement:OpaquePointer? = nil  //语句的指针
        /**
            参数说明：
                1. 已打开的数据库句柄
                2. 执行的sql语句
                3. 以字节为单位的sql语句长度，-1表示自动计算
                4. 语句句柄，据此获取查询结果，需要调用 sqlite3_finalize 释放
                5. 未使用的指针地址，通常传入nil
        **/
        let code = sqlite3_prepare_v2(database, cSql, -1, &statement, nil)
        
        //执行不成功
        if code != SQLITE_OK{
            sqlite3_finalize(statement)
            print("执行 \(sql) 错误, err code: " + String.init(code))
            let errmsg = sqlite3_errmsg(database)
            if errmsg != nil{
                print(errmsg!)
            }
            return nil
        }
           
        //成功：需要从 statement 里面取数据，定义 rows 变量来接收数据
        var rows = [[String:AnyObject]]()
        
        //每次取 1 行
        while sqlite3_step(statement) == SQLITE_ROW{
            rows.append(record(stmt: statement!))
        }
           
        //最后释放空间
        sqlite3_finalize(statement)
           
        //返回结果
        return rows
           
    }
    
    func execUpdateSQL(sql:String) -> Bool {
        let cSql = sql.cString(using: String.Encoding.utf8)!  //对sql语句进行utf8编码
        let code = sqlite3_exec(database,cSql, nil, nil, nil)
        if code != SQLITE_OK {
            print("执行 \(sql) 错误, err code: " + String.init(code))
            let errmsg = sqlite3_errmsg(database)
            if errmsg != nil{
                print(errmsg!)
            }
            return false
        }
        return true
    }
    
    //读一行记录一行
    private func record(stmt:OpaquePointer)->[String:AnyObject]{
        var row = [String:AnyObject]()
        
        //遍历所有列，获取每一列的信息
        for col in 0..<sqlite3_column_count(stmt){
            let cName = sqlite3_column_name(stmt, col) //获取列名
            let name = String(cString: cName!,encoding: String.Encoding.utf8)
            
            var value:AnyObject?
            
            switch sqlite3_column_type(stmt, col) {
                case SQLITE_FLOAT:
                    value = sqlite3_column_double(stmt, col) as AnyObject
                case SQLITE_INTEGER:
                    value = Int(sqlite3_column_int(stmt, col)) as AnyObject
                case SQLITE_TEXT:
                    let cText = sqlite3_column_text(stmt, col)
                    value = String.init(cString: cText!) as AnyObject
                case SQLITE_NULL:
                    value = NSNull()
                default:
                    print("不支持的数据类型")
            }
            row[name!] = value ?? NSNull()
        }
        return row
    }
}
